--    Program: 		CAPARIMP_INVOICE_ADJUST.sql
--    Description:  Import AR INVOICE ADJUST information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_adjustment_id			number;
                    		                 		
   cursor cur_ar_invoice_adjust is
      select * from CRST_AR_ADJUSTMENTS_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_invoice_adjust loop   	
     l_err_msg_tmp := '';
     
     SELECT AR_ADJUSTMENTS_S.NEXTVAL 
     INTO l_adjustment_id
     FROM DUAL;
     
     update crst_ar_adjustments_all
     set    adjustment_id_n = l_adjustment_id
     where  adjustment_id = cur.ADJUSTMENT_ID;
     
     begin
       -- import data
       insert into AR_ADJUSTMENTS_ALL
       (
          ADJUSTMENT_ID                   ,
          LAST_UPDATED_BY                 ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATE_LOGIN               ,
          CREATED_BY                      ,
          CREATION_DATE                   ,
          AMOUNT                          ,
          APPLY_DATE                      ,
          GL_DATE                         ,
          SET_OF_BOOKS_ID                 ,
          CODE_COMBINATION_ID             ,
          TYPE                            ,
          ADJUSTMENT_TYPE                 ,
          STATUS                          ,
          LINE_ADJUSTED                   ,
          FREIGHT_ADJUSTED                ,
          TAX_ADJUSTED                    ,
          RECEIVABLES_CHARGES_ADJUSTED    ,
          ASSOCIATED_CASH_RECEIPT_ID      ,
          CHARGEBACK_CUSTOMER_TRX_ID      ,
          BATCH_ID                        ,
          CUSTOMER_TRX_ID                 ,
          CUSTOMER_TRX_LINE_ID            ,
          SUBSEQUENT_TRX_ID               ,
          PAYMENT_SCHEDULE_ID             ,
          RECEIVABLES_TRX_ID              ,
          DISTRIBUTION_SET_ID             ,
          GL_POSTED_DATE                  ,
          COMMENTS                        ,
          AUTOMATICALLY_GENERATED         ,
          CREATED_FROM                    ,
          REASON_CODE                     ,
          POSTABLE                        ,
          APPROVED_BY                     ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          POSTING_CONTROL_ID              ,
          ACCTD_AMOUNT                    ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          REQUEST_ID                      ,
          ADJUSTMENT_NUMBER               ,
          ORG_ID                          ,
          USSGL_TRANSACTION_CODE          ,
          USSGL_TRANSACTION_CODE_CONTEXT  ,
          DOC_SEQUENCE_VALUE              ,
          DOC_SEQUENCE_ID                 ,
          ASSOCIATED_APPLICATION_ID       ,
          CONS_INV_ID                     ,
          MRC_GL_POSTED_DATE              ,
          MRC_POSTING_CONTROL_ID          ,
          MRC_ACCTD_AMOUNT                ,
          ADJ_TAX_ACCT_RULE               ,
          GLOBAL_ATTRIBUTE_CATEGORY       ,
          GLOBAL_ATTRIBUTE1               ,
          GLOBAL_ATTRIBUTE2               ,
          GLOBAL_ATTRIBUTE3               ,
          GLOBAL_ATTRIBUTE4               ,
          GLOBAL_ATTRIBUTE5               ,
          GLOBAL_ATTRIBUTE6               ,
          GLOBAL_ATTRIBUTE7               ,
          GLOBAL_ATTRIBUTE8               ,
          GLOBAL_ATTRIBUTE9               ,
          GLOBAL_ATTRIBUTE10              ,
          GLOBAL_ATTRIBUTE11              ,
          GLOBAL_ATTRIBUTE12              ,
          GLOBAL_ATTRIBUTE13              ,
          GLOBAL_ATTRIBUTE14              ,
          GLOBAL_ATTRIBUTE15              ,
          GLOBAL_ATTRIBUTE16              ,
          GLOBAL_ATTRIBUTE17              ,
          GLOBAL_ATTRIBUTE18              ,
          GLOBAL_ATTRIBUTE19              ,
          GLOBAL_ATTRIBUTE20              ,
          LINK_TO_TRX_HIST_ID              
       )
       values
       (
            l_adjustment_id                     ,     
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_DATE                ,
            cur.LAST_UPDATE_LOGIN               ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.CREATION_DATE                   ,
            cur.AMOUNT                          ,
            cur.APPLY_DATE                      ,
            cur.GL_DATE                         ,
            CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID),
            cur.TYPE                            ,
            cur.ADJUSTMENT_TYPE                 ,
            cur.STATUS                          ,
            cur.LINE_ADJUSTED                   ,
            cur.FREIGHT_ADJUSTED                ,
            cur.TAX_ADJUSTED                    ,
            cur.RECEIVABLES_CHARGES_ADJUSTED    ,
            cur.ASSOCIATED_CASH_RECEIPT_ID      , --NULL
            CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.CHARGEBACK_CUSTOMER_TRX_ID),
            CAPARIMP_MAPPING.GetInvoiceBatchIDMapping(cur.BATCH_ID),
            CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.CUSTOMER_TRX_ID),
            CAPARIMP_MAPPING.GetCustomerTrxLineIDMapping(cur.CUSTOMER_TRX_LINE_ID),
            cur.SUBSEQUENT_TRX_ID               ,
            CAPARIMP_MAPPING.GetPaymentScheduleIDMapping(cur.PAYMENT_SCHEDULE_ID),
            CAPARIMP_MAPPING.GetReceivableActivityIDMapping(cur.ORG_ID, cur.RECEIVABLES_TRX_ID),
            CAPARIMP_MAPPING.GetDistributionSetIDMapping(cur.DISTRIBUTION_SET_ID),
            cur.GL_POSTED_DATE                  ,
            cur.COMMENTS                        ,
            cur.AUTOMATICALLY_GENERATED         ,
            cur.CREATED_FROM                    ,
            cur.REASON_CODE                     ,
            cur.POSTABLE                        ,
            cur.APPROVED_BY                     ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.POSTING_CONTROL_ID              ,
            cur.ACCTD_AMOUNT                    ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.REQUEST_ID                      ,
            cur.ADJUSTMENT_NUMBER               ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.USSGL_TRANSACTION_CODE          ,
            cur.USSGL_TRANSACTION_CODE_CONTEXT  ,
            cur.DOC_SEQUENCE_VALUE              ,
            cur.DOC_SEQUENCE_ID                 ,
            cur.ASSOCIATED_APPLICATION_ID       ,
            cur.CONS_INV_ID                     ,
            cur.MRC_GL_POSTED_DATE              ,
            cur.MRC_POSTING_CONTROL_ID          ,
            cur.MRC_ACCTD_AMOUNT                ,
            cur.ADJ_TAX_ACCT_RULE               ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.LINK_TO_TRX_HIST_ID                                     
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_INVOICE_ADJUST', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
